﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_AB_Interface_Update]
(
	@id INT,
	@CompanyId INT,
	@CompanyName NVARCHAR(50),
	@ProductId VARCHAR(200),
	@ProductName NVARCHAR(50)
)

AS
DECLARE @Count_Item INT
DECLARE @Count_List INT
BEGIN
	
	DECLARE @New_ProductId VARCHAR(200)
	SET @New_ProductId=''
	
	DECLARE @SourceSql   varchar(8000),@StrSeprate   varchar(100)
	SET @SourceSql=@ProductId+','
	SET @StrSeprate=','
	DECLARE @ch varchar(100)
	DECLARE @PName NVARCHAR(300)
	SET @PName=''

	--预存帐号个数
	 SELECT @Count_Item=COUNT(0) FROM AB_Interface WHERE syzh=@id
	--存在的记录数
	 SELECT @Count_List=COUNT(0) FROM AB_list WHERE Iid=@id
	
	IF @Count_List=0
	BEGIN
		
		WHILE(@SourceSql<>'')   
			BEGIN   
				set   @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1)   
				--SELECT  @ch 
				--判断该公司的所有接口帐号是否已经添加过该产品
				IF (SELECT COUNT(0) FROM AB_Interface WHERE ProductId LIKE '%{'+@ch+'}%' AND CompanyId=@CompanyId AND syzh=0 AND id<>@id)>0 
					BEGIN 
						--SET @PName=@PName+','+(SELECT Title FROM Product WHERE id=@ch)
						SELECT Title FROM Product WHERE id=@ch
						RETURN 
					END
				ELSE 
					BEGIN --如果不重复 将原有产品ID和当前不重复的ID组合成新的字符串
						SET @New_ProductId=@New_ProductId+'{'+@ch+'}'
					END
				set   @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')  
			END 
		
		IF @Count_Item=0
			BEGIN
				--更新
				UPDATE [AB_Interface]
				SET [CompanyId] = @CompanyId
				,[Companyname] = @CompanyName
				,[ProductId] = @New_ProductId --带{}的新产品ID字符串
				,[Productname] = @ProductName
				WHERE id=@id AND syzh=0
				
				SELECT 1
			END
		ELSE
			BEGIN
				--更新
				UPDATE [AB_Interface]
				SET [CompanyId] = @CompanyId
				,[Companyname] = @CompanyName
				,[ProductId] = @New_ProductId --带{}的新产品ID字符串
				,[Productname] = @ProductName
				WHERE id=@id AND syzh=0
				
				--更新其它
				UPDATE [AB_Interface]
				SET [ProductId] = @New_ProductId --带{}的新产品ID字符串
				,[Productname] = @ProductName+'(预存)'
				WHERE syzh=@id
				
				SELECT 1
			END
			
	END
	ELSE
		BEGIN
			SELECT -1
		END

	--SET NOCOUNT ON;

END
